package com.ort.ezcalendar.persistance;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class EventHelper extends SQLiteOpenHelper {

	private static final String DATABASE_NAME = "ezCalendar.db";
	private static final int SCHEMA_VERSION = 2;

	public EventHelper(Context context) {
		super(context, DATABASE_NAME, null, SCHEMA_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL("CREATE TABLE events (_id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, place TEXT, "
				+ "date INT, description TEXT, category TEXT, photo TEXT, has_alarm INTEGER,"
				+ "latitude REAL, longitude REAL);");
		db.execSQL("CREATE TABLE alarm (_id INTEGER PRIMARY KEY AUTOINCREMENT, event_id INTEGER,date INT, "
				+ "FOREIGN KEY(event_id) REFERENCES events(_id));");
		db.execSQL("CREATE TABLE category (_id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT);");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		if (oldVersion == 1) {
			db.execSQL("ALTER TABLE events RENAME TO tmp_events;");
			db.execSQL("CREATE TABLE events (_id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, place TEXT, "
					+ "date INT, description TEXT, category TEXT, photo TEXT, has_alarm INTEGER,"
					+ "latitude REAL, longitude REAL);");
			db.execSQL("INSERT INTO events(_id, name, place, date, description, category, photo, has_alarm, latitude, longitude"
					+ " SELECT _id, name, place, inicialDate, description, category, NULL, NULL, NULL, NULL"
					+ " FROM tmp_events;");
			db.execSQL("DROP TABLE tmp_events;");
			db.execSQL("CREATE TABLE alarm (_id INTEGER PRIMARY KEY AUTOINCREMENT, event_id INTEGER,date INT, "
					+ "FOREIGN KEY(event_id) REFERENCES events(_id));");
			db.execSQL("CREATE TABLE category (_id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT);");

		}

	}

	public Cursor getAllEvents() {
		return (getReadableDatabase()
				.rawQuery(
						"SELECT _id, name, place, date, description, category, photo, has_alarm, latitude, longitude "
								+ "FROM events ORDER BY name", null));
	}

	public Cursor getAllAlarms() {
		return (getReadableDatabase().rawQuery(
				"SELECT _id, event_id, date FROM alarm", null));
	}
	
	public List<String> getAllAlarms(int idEvent) {
		 Cursor c =(getReadableDatabase().rawQuery(
				"SELECT _id, event_id, date FROM alarm WHERE event_id = " + idEvent, null));
		 c.moveToFirst();
		 List<String> retorno = new ArrayList<String>();
		 while(!c.isAfterLast()){
			 retorno.add(String.valueOf(getAlarm(c).getTime()));
			 c.moveToNext();
		 }
		 c.close();
		 return retorno;
	}

	private Calendar normalizarFecha(Calendar fecha) {
		Calendar fechaNueva = fecha;
		fechaNueva.set(Calendar.HOUR_OF_DAY, 0);
		fechaNueva.set(Calendar.MINUTE, 0);
		fechaNueva.set(Calendar.SECOND, 0);
		fechaNueva.set(Calendar.MILLISECOND, 0);
		return fechaNueva;
	}

	public Cursor getEventos(Calendar fecha) {
		Calendar fechaAux = normalizarFecha(fecha);
		long desde = fechaAux.getTimeInMillis();
		fechaAux.add(Calendar.DAY_OF_MONTH, 1);
		long hasta = fechaAux.getTimeInMillis();

		return (getReadableDatabase()
				.rawQuery(
						"SELECT _id, name, place, date, description, category, photo, has_alarm, latitude, longitude "
								+ "FROM events WHERE (date BETWEEN '"
								+ desde
								+ "' AND '" + hasta + "') ORDER BY name", null));

	}

	public Cursor getEvento(long id) {
		return (getReadableDatabase()
				.rawQuery(
						"SELECT _id, name, place, date, description, category, photo, has_alarm, latitude, longitude "
								+ "FROM events WHERE (_id = '"
								+ id
								+ "') ORDER BY name", null));
	}

	public Integer[] getCantidadEventosMes(int anio, int mes) {
		Calendar fechaAux = Calendar.getInstance();
		fechaAux.set(anio, mes, 1);
		Integer[] retorno = new Integer[fechaAux
				.getActualMaximum(Calendar.DAY_OF_MONTH) + 1];
		for (int i = 0; i < retorno.length; i++)
			retorno[i] = 0;
		long desde = fechaAux.getTimeInMillis();
		fechaAux.add(Calendar.MONTH, 1);
		long hasta = fechaAux.getTimeInMillis();
		Cursor c = getReadableDatabase()
				.rawQuery(
						"SELECT _id, name, place, date, description, category, photo, has_alarm, latitude, longitude "
								+ "FROM events "
								+ "WHERE date BETWEEN '"
								+ desde + "' AND '" + hasta + "' ORDER BY name",
						null);
		c.moveToFirst();
		while (!c.isAfterLast()) {
			int dia = this.getDate(c).getDate();
			retorno[dia]++;

			c.moveToNext();
		}
		return retorno;
	}

	public long insertNewEvent(String name, String place, Date dateEvent,
			String description, String category, String photoURL,
			boolean hasAlarm, double latitude, double longitude) {
		ContentValues cv = new ContentValues();

		cv.put("name", name);
		cv.put("place", place);
		cv.put("date", dateEvent.getTime());
		cv.put("description", description);
		cv.put("category", category);
		cv.put("photo", photoURL);
		cv.put("has_alarm", hasAlarm);
		cv.put("latitude", latitude);
		cv.put("longitude", longitude);

		return getWritableDatabase().insert("events", "name", cv);
	}

	public void editEvent(int id, String name, String place, Date dateEvent,
			String description, String category, String photoURL,
			boolean hasAlarm, double latitude, double longitude){
		ContentValues cv = new ContentValues();
		cv.put("name", name);
		cv.put("place", place);
		cv.put("date", dateEvent.getTime());
		cv.put("description", description);
		cv.put("category", category);
		cv.put("photo", photoURL);
		cv.put("has_alarm", hasAlarm);
		cv.put("latitude", latitude);
		cv.put("longitude", longitude);
		getWritableDatabase().update("events", cv, "_id=" + id, null);
	}
	
	public void deleteEvent(int id){
		getWritableDatabase().delete("events", "_id=" + id, null);
		getWritableDatabase().delete("alarm", "event_id=" + id, null);
	}

	public void insertNewAlarm(long idEvent, Date date) {
		ContentValues cv = new ContentValues();

		cv.put("event_id", idEvent);
		cv.put("date", date.getTime());
		getWritableDatabase().insert("alarm", "date", cv);
	}

	public void insertNewCategory(String name) {
		ContentValues cv = new ContentValues();
		cv.put("name", name);

		getWritableDatabase().insert("category", "name", cv);
	}
	
	public void resetUserValues() {
		getReadableDatabase().execSQL("DROP TABLE alarm;");
		getReadableDatabase().execSQL("DROP TABLE events;");
		getReadableDatabase().execSQL("CREATE TABLE events (_id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, place TEXT, "
				+ "date INT, description TEXT, category TEXT, photo TEXT, has_alarm INTEGER,"
				+ "latitude REAL, longitude REAL);");
		getReadableDatabase().execSQL("CREATE TABLE alarm (_id INTEGER PRIMARY KEY AUTOINCREMENT, event_id INTEGER,date INT, "
				+ "FOREIGN KEY(event_id) REFERENCES events(_id));");

	}
	
	public void resetCategories() {
		getReadableDatabase().execSQL("DROP TABLE category;");
		getReadableDatabase().execSQL("CREATE TABLE category (_id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT);");
	}

	public int getId(Cursor c) {
		return c.getInt(0);
	}

	public String getName(Cursor c) {
		return (c.getString(1));
	}

	public String getPlace(Cursor c) {
		return (c.getString(2));
	}

	public Date getDate(Cursor c) {
		Calendar fecha = Calendar.getInstance();
		fecha.setTimeInMillis(c.getLong(3));
		return (fecha.getTime());
	}

	public String getPhoto(Cursor c) {
		return c.getString(6);
	}

	public String getDescription(Cursor c) {
		return c.getString(4);
	}

	public String getCategory(Cursor c) {
		return c.getString(5);
	}

	public double getLatitude(Cursor c) {
		return c.getDouble(8);
	}

	public double getLongitude(Cursor c) {
		return c.getDouble(9);
	}

	public Date getAlarm(Cursor c) {
		Calendar fecha = Calendar.getInstance();
		fecha.setTimeInMillis(c.getLong(2));
		return (fecha.getTime());
	}

	public int getIdEvento(Cursor c) {
		return c.getInt(1);
	}

}
